﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace Sociedad.DAL
{
    public class SolicitudDesignacionDAL
    {
        EntityConnectionSOA context = new EntityConnectionSOA();

        public List<vw_Listar_SolicitudDesignacion> Listar_SolicitudDesignacion_Vista()
        {
            try
            {
                List<vw_Listar_SolicitudDesignacion> lstSol = new List<vw_Listar_SolicitudDesignacion>();
                var lst = from lista in context.vw_Listar_SolicitudDesignacion
                          select lista;
                if (lst.Count() > 0)
                {
                    lstSol = lst.ToList();
                }
                return lstSol;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public List<vw_Listar_SolicitudDesignacion> Listar_SolicitudDesignacion_Vista(String pCodUO)
        {
            try
            {
                List<vw_Listar_SolicitudDesignacion> lstSol = new List<vw_Listar_SolicitudDesignacion>();
                var lst = from lista in context.vw_Listar_SolicitudDesignacion
                          where lista.csde_OrgAud == pCodUO
                          select lista;
                if (lst.Count() > 0)
                {
                    lstSol = lst.ToList();
                }
                return lstSol;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public soa_SolicitudDesignacion Buscar_SolicitudDesignacion(String pCod)
        {
            try
            {
                soa_SolicitudDesignacion beSol = new soa_SolicitudDesignacion();
                var sol = from solic in context.soa_SolicitudDesignacion
                          where solic.csde_Codigo == pCod
                          select solic;
                if (sol.Count() > 0)
                {
                    beSol = sol.First();
                }
                return beSol;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public String Insertar_SolicitudDesignacion(soa_SolicitudDesignacion oSolic)
        {
            String cRpta = "0@";
            try
            {
                var parameters = new[] { 
                    new SqlParameter("@0", oSolic.csde_OrgAud),
                    new SqlParameter("@1", oSolic.csde_EntAud ),
                    new SqlParameter("@2", oSolic.cprm_TipAud ),
                    new SqlParameter("@3", oSolic.dsde_IniExa ),
                    new SqlParameter("@4", oSolic.dsde_FinExa ),
                    new SqlParameter("@5", oSolic.csde_Comentario ),
                    new SqlParameter("@6", oSolic.csde_UsuCre ),
                    new SqlParameter("@7", oSolic.cprm_Estado ),
                    new SqlParameter("@8", oSolic.csde_Objetivo ),
                    new SqlParameter("@9", oSolic.nsde_Plazo ),
                    new SqlParameter("@10", oSolic.nsde_CosTot ),
                    new SqlParameter("@11", SqlDbType.VarChar, 100) { Direction = ParameterDirection.Output }        
                };

                context.ExecuteStoreCommand("exec pa_Ins_SolicitudDesignacion @_csde_OrgAud=@0, @_csde_EntAud =@1,@_cprm_TipAud =@2,@_dsde_IniExa =@3,@_dsde_FinExa =@4,@_csde_Comentario =@5,@_csde_UsuCre =@6,@_cprm_Estado =@7,@_csde_Objetivo =@8,@_nsde_Plazo =@9,@_nsde_CosTot =@10, @_ResultMsj=@11 output", parameters);

                cRpta = (String)parameters[11].Value;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return cRpta;
        }

        public String Actualizar_SolicitudDesignacion(soa_SolicitudDesignacion oSolic)
        {
            String cRpta = "0@";
            try
            {
                var parameters = new[] { 
                    new SqlParameter("@0", oSolic.csde_OrgAud),
                    new SqlParameter("@1", oSolic.csde_EntAud ),
                    new SqlParameter("@2", oSolic.cprm_TipAud ),
                    new SqlParameter("@3", oSolic.dsde_IniExa ),
                    new SqlParameter("@4", oSolic.dsde_FinExa ),
                    new SqlParameter("@5", oSolic.csde_Comentario ),
                    new SqlParameter("@6", oSolic.csde_UsuCre ),
                    new SqlParameter("@7", oSolic.cprm_Estado ),
                    new SqlParameter("@8", oSolic.csde_Objetivo ),
                    new SqlParameter("@9", oSolic.nsde_Plazo ),
                    new SqlParameter("@10", oSolic.nsde_CosTot ),
                    new SqlParameter("@11", oSolic.csde_Codigo ),
                    new SqlParameter("@12", SqlDbType.VarChar, 100) { Direction = ParameterDirection.Output }        
                };

                context.ExecuteStoreCommand("exec pa_Upd_SolicitudDesignacion @_csde_OrgAud=@0, @_csde_EntAud =@1,@_cprm_TipAud =@2,@_dsde_IniExa =@3,@_dsde_FinExa =@4,@_csde_Comentario =@5,@_csde_UsuCre =@6,@_cprm_Estado =@7,@_csde_Objetivo =@8,@_nsde_Plazo =@9,@_nsde_CosTot =@10, @_csde_Codigo = @11, @_ResultMsj=@12 output", parameters);

                cRpta = (String)parameters[12].Value;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return cRpta;
        }

        public String Eliminar_SolicitudDesignacion(String pCod)
        {
            String cRpta = "0@";
            try
            {
                //soa_SolicitudDesignacion beSoc = context.soa_SolicitudDesignacion.First(i => i.csde_Codigo == pCod);
                //beSoc.csde_FlgEli = "S";
                //context.SaveChanges();
                //cRpta = "1@" + beSoc.csde_Codigo;
                var parameters = new[] { 
                    new SqlParameter("@0", pCod),
                    new SqlParameter("@1", SqlDbType.VarChar, 100) { Direction = ParameterDirection.Output }        
                };

                context.ExecuteStoreCommand("exec pa_Del_SolicitudDesignacion @_csde_Codigo = @0, @_ResultMsj=@1 output", parameters);
                cRpta = (String)parameters[1].Value;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return cRpta;
        }
    }
}
